﻿#-*- coding: utf-8 -*-

import win32serviceutil
import win32service 
import win32event 
import os
import re
import time
import ctypes
import decimal
import pymssql
import datetime

## RESULTPATH: 测试结果文件存放的目录
RESULTPATH      = 'D:\\AutoTestFile'
## BACKUPPATH: 备份文件的存放目录
BACKUPPATH      = 'D:\\TestingDataBackup'

## MAIN_PATTERN: 测试的产品概括数据, 数据格式如下:
##      EVENTSTART|1|2015030602203400|WO TESTING-FINAL|Rosenberger_QP||A|SHENGCHAN002|Agilent Technologies,E5061B,MY49203606,A.02.11||||Passive SWR||23232132|||DFT-09-30-D-01||rapb1321150900789|||||YES|||013036||
MAIN_PATTERN    = r'EVENTSTART\|1\|(\d+)\|(.*)\|(.*)\|\|(.*)\|(.*)\|(.*)\|\|\|\|(.*)\|\|(.*)\|\|\|(.*)\|\|(.*)\|\|\|\|\|YES\|\|\|(.*)\|\|'
## INFO_PATTERN: 各测试项的数据, 数据格式如下:
##      PRODUCTMEASURE|1|VSWR -45 Port 1||PASS|||1.027|Degree|1710|1880|||||||||||1|1.48|SWR/ISO Testing 1|||
INFO_PATTERN    = r'PRODUCTMEASURE\|1\|(.*)\|\|(.*)\|\|\|(.*)\|(.*)\|(.*)\|(.*)\|\|\|\|\|\|\|\|\|\|\|(.*)\|(.*)\|(.*)\|\|\|'
## STOP_PATTERN: 测试数据结束, 数据格式如下:
##      EVENTSTOP|1|PASS|0||1.0.0|||||
STOP_PATTERN    = r'EVENTSTOP\|1\|(.*)\|(.*)\|\|(.*)\|\|\|\|\|'

## the following for PIA test
## f1datrp: PIA测试的f1数据，数据格式如下：
## f1 0939.0 MHz START 0933.0 MHz STOP 0939.0 MHz POWER +43.0 dBm
f1datrp = r'^f1\s(\d+\.\d+)\sMHz\sSTART\s\d+\.\d+\sMHz\sSTOP\s\d+\.\d+\sMHz\sPOWER\s[-+]\d+\.\d+\s.*$'
## untyprp: PIA测试的单位，类型，数据格式如下：
## MEAS IM9 UNIT dBm SWEEP
untyprp = r'^MEAS\s.*\sUNIT\s(.*)\s(.*)$'
## machnrp: PIA测试机台编号，数据格式如下：
## Model Nr. IM-0710, SERIAL 11451
machnrp = r'^Model(.*)SERIAL(.*)'
## timedrp: PIA测试时间，数据格式如下：
## 20121011 13:06
timedrp = r'^(\d{8}\s\d{2}:\d{2})$'
## testdrp: PIA测试数据，数据格式如下：
## 0785.0;-141.90
testdrp = r'^\d+\.\d+;([-+].*)$'
## limitrp: PIA测试的下限，数据格式如下：
## Limit Line: -125.0
limitrp = r'^Limit Line:\s*(.*)$'
## sernorp: PIA测试的产品序号，数据格式如下：
## Ser No: 123
sernorp = r'^Ser No:\s*(.*)$'
## usridrp: PIA测试的操作人员，数据格式如下：
## USER: 004299
usridrp = r'^User ID:\s*(.*)$'
## statnrp: PIA测试所在的Shop Floor站点，数据格式如下：
## STATION: WO TESTING-FINAL
statnrp = r'^station id:\s*(.*)$'
## 从ant文件名获取端口号
## RAPA506142300129_PORT1.txt
fileant = r'.*\_PORT(\d+)\.txt'
## 从pas文件获取线别
## PIM Line: A
linesrp = r'^PIM Line:\s*(.*)$'
## 获取测试程序版本
## ATE Version: 4.8.1.2
versnrp = r'^ATE Version:\s*(.*)$'
## 获取相位测试 Beam Peak
## Beam Peak Port 1
beamprp = r'^Beam Peak Port (\d+)$'
## 获取相位测试 Beam Width
## HPBW Port 1
beamwrp = r'^HPBW Port (\d+)$'
## 获取驻波测试 VSWR
## VSWR -45 Port 1
vswrsrp = r'^VSWR\s+.*\s+Port\s+(\d+)$'
## 获取隔离测试 Isolation
## Isolation Port 1/2
isolarp = r'^Isolation Port\s+(\d+)/(\d+)$'

errlist = []

class BaseTestObj(object):
    def __init__(self, path, dbname):
        self.dbname         = dbname
        self.db             = self.getDB()
        self.testfile       = path
        self.filedict       = {}
        self.ssns           = ""
        self.port           = ""
        self.specification  = ""
        self.result         = "PASS"
        self.operator       = ""
        self.testdata       = "0.0"
        self.line           = ""
        self.machine        = ""
        self.version        = ""
        self.savepath       = ""
        self.testtime       = ""
        self.progtype       = ""
        self.errors         = ""

    def getDB(self):
        db = None
        try:
            db = pymssql.connect(
                host        = '192.168.1.36',
                database    = self.dbname,
                user        = 'sa',
                password    = '1234567890'
            )
            return db
        except Exception, e:
            errlist.append(e)

        return None

    def getFrequency(self, f1):
        d900  = abs(float(f1) - float(900))
        d1800 = abs(float(f1) - float(1800))
        d2100 = abs(float(f1) - float(2100))
        d2300 = abs(float(f1) - float(2300))
        d2600 = abs(float(f1) - float(2600))
        valDict = {
            d900 : "900",
            d1800: "1800",
            d2100: "2100",
            d2300: "2300",
            d2600: "2600"
        }

        return valDict[min(valDict)]

    def backupFile(self):
        for path, srcfile in self.filedict.items():
            f_src  = None
            f_tar  = None
            target = os.path.dirname(path)
            if not os.path.isdir(target):
                os.makedirs(target)
            filename  = os.path.basename(srcfile)
            name, exp = os.path.splitext(filename)
            try:
                f_src = open(srcfile, 'r')
                try:
                    f_tar = open(path, 'w')
                    lines = f_src.readlines()
                    f_tar.writelines(lines)
                except Exception, e:
                    errlist.append("backupFile error: %s"%e)
                finally:
                    if f_tar:
                        f_tar.close()
            except Exception, e:
                errlist.append("backupFile error: %s"%e)
            finally:
                if f_src:
                    f_src.close()

class PiaObj(BaseTestObj):
    def __init__(self, path, dbname):
        super(PiaObj, self).__init__(path, dbname)
        self.station    = "WO TESTING-PIM-FINAL"
        self.frequency  = ""

    def save(self):
        if not self.db:
            errlist.append("Can't connected to DATABASE: %s"%self.dbname)
            raise Exception, "Error occured: Can't connected to DATABASE."

        sql = "sp_TestDataForAntennaPim %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s"
        rst = getOneRow(self.db, sql, self.ssns, self.station, self.frequency, self.port, self.specification, self.testdata, 
            self.result, self.savepath, self.line, self.machine, self.version, self.testtime, self.operator)
        self.db.commit()

    def analysis(self):
        f      = None
        tstd_p = re.compile(testdrp)
        limt_p = re.compile(limitrp)
        sesn_p = re.compile(sernorp)
        file_p = re.compile(fileant, re.I)
        freq_p = re.compile(f1datrp)
        user_p = re.compile(usridrp)
        line_p = re.compile(linesrp)
        vers_p = re.compile(versnrp)
        mach_p = re.compile(machnrp)
        time_p = re.compile(timedrp)
        try:
            f       = open(self.testfile, 'r')
            lines   = f.readlines()
            fp      = file_p.findall(self.testfile)
            if fp:
                self.port = "CH%s"%str(fp[0])
            else:
                self.errors = "<%s> can't get the port info"%self.testfile
                return
            for line in lines:
                freq = freq_p.findall(line)
                if freq:
                    self.frequency = self.getFrequency(freq[0]) # 根据测试文件内容判定是哪个频段
                    continue
                ssns = sesn_p.findall(line)
                if ssns:
                    self.ssns = ssns[0]
                    sql = """
                        SELECT A.LIMIT 
                          FROM SFC_ITEMS A(NOLOCK), SFC_WO B(NOLOCK), SFC_SSN C(NOLOCK)
                         WHERE C.SSN_NO=%s 
                           AND B.WONO=C.WONO 
                           AND B.ITEM=A.ITEM
                    """
                    rst = getOneRow(self.db, sql, self.ssns)
                    if not rst:
                        self.errors = "<%s> not set the test limit"%self.testfile
                        return
                    if rst:
                        limits = str(rst[0]).split(",")
                        if int(fp[0]) > len(limits):
                            self.errors = "<%s> port number more than limit number"%self.testfile
                            return
                    continue
                specification = limt_p.findall(line)
                if specification:
                    self.specification = specification[0]
                    continue
                data = tstd_p.findall(line)
                if data:
                    if self.testdata == "0.0":
                        self.testdata = data[0]
                    else:
                        if float(self.testdata) < float(data[0]):
                            self.testdata = data[0]
                    continue
                user = user_p.findall(line)
                if user:
                    self.operator = str(user[0]).strip()
                version = vers_p.findall(line)
                if version:
                    self.version = str(version[0]).strip()
                machine = mach_p.findall(line)
                if machine:
                    self.machine = str(machine[0][0]).strip() + " " + str(machine[0][1]).strip()
                testtime = time_p.findall(line)
                if testtime:
                    self.testtime = str(testtime[0]).strip()
                currline = line_p.findall(line)
                if currline:
                    self.line = str(currline[0]).strip()
                    break
            if float(self.testdata) > float(self.specification):
                self.result = "FAIL"
        except Exception, e:
            errlist.append("analysis pia file error: %s"%e)
        finally:
            if f:
                f.close()

class PasObj(BaseTestObj):
    def __init__(self, path, dbname):
        super(PasObj, self).__init__(path, dbname)
        self.station    = "WO TESTING-PIM"
        self.frequency  = ""
        self.freqport   = ""
        self.totalport  = 0

    def save(self):
        if not self.db:
            errlist.append("Can't connected to DATABASE: %s"%self.dbname)
            raise Exception, "Error occured: Can't connected to DATABASE."

        sql = "sp_TestDataAutoTransForPim %s, %s, %s, %s, %s, %s, %s, %d, %s, %s, %s, %s, %s, %s, %s"
        rst = getOneRow(self.db, sql, self.ssns, self.station, self.port, self.frequency,
            self.specification, self.testdata, self.freqport, self.totalport, self.result, 
            self.savepath, self.line, self.machine, self.version, self.testtime, self.operator)
        self.db.commit()

    def analysis(self):
        f      = None
        tstd_p = re.compile(testdrp)
        limt_p = re.compile(limitrp)
        sesn_p = re.compile(sernorp)
        file_p = re.compile(fileant, re.I)
        freq_p = re.compile(f1datrp)
        user_p = re.compile(usridrp)
        line_p = re.compile(linesrp)
        vers_p = re.compile(versnrp)
        mach_p = re.compile(machnrp)
        time_p = re.compile(timedrp)
        try:
            f       = open(self.testfile, 'r')
            lines   = f.readlines()
            fp      = file_p.findall(self.testfile)
            if fp:
                self.port = "CH%s"%str(fp[0])
            else:
                self.errors = "<%s> can't get the port info"%self.testfile
                return
            for line in lines:
                freq = freq_p.findall(line)
                if freq:
                    self.frequency = self.getFrequency(freq[0]) # 根据测试文件内容判定是哪个频段
                    continue
                ssns = sesn_p.findall(line)
                if ssns:
                    self.ssns = ssns[0]
                    sql = """
                        SELECT A.FREQUENCY, A.SPECIFICATION 
                          FROM SFC_PIMSETTING A(NOLOCK), SFC_WO B(NOLOCK), SFC_SSN C(NOLOCK)
                         WHERE C.SSN_NO=%s
                           AND B.WONO=C.WONO 
                           AND B.ITEM=A.ITEM
                           AND A.STATION=C.CURR_STATION
                    """
                    rst = getOneRow(self.db, sql, self.ssns)
                    if not rst:
                        self.errors = "<%s> not set the test limit"%self.testfile
                        return  # 如果该物料还未设置频段及指标

                    freqs = str(rst[0]).split(",")
                    freqs = [str(x).strip() for x in freqs]
                    limits = str(rst[1]).split(";")
                    if len(limits) > 1 and len(limits) != len(freqs):
                        self.errors = "<%s> frequency number not eq the limit number"%self.testfile
                        return  # 如果指标按照不同频段设置了, 但是设置的不完全

                    freqidx = -1
                    currlimits = limits[0]
                    if self.frequency in freqs:
                        freqidx = freqs.index(self.frequency)
                    if freqidx == -1:
                        self.errors = "<%s> not include this frequency"%self.testfile
                        return  # 如果物料设置的频段中不包括测试文件中给出的频段

                    if len(limits) > 1:
                        currlimits = limits[freqidx]
                    specis = str(currlimits).split(",")
                    specis = [str(x).strip() for x in specis]

                    if len(limits) == 1:
                        self.totalport = len(specis) * len(freqs)
                    else:
                        for v in limits:
                            vals = str(v).split(",")
                            self.totalport += len(vals)
                    for v in freqs:
                        if self.freqport == "":
                            self.freqport = str(int(v) + len(specis))
                        else:
                            self.freqport = self.freqport + "," + str(int(v) + len(specis))
                    idx = 0
                    dict_spec = {}
                    for v in specis:
                        idx += 1
                        vals = str(v).split(":")
                        if len(vals) == 1:
                            dict_spec[str(idx)] = vals[0]
                        else:
                            dict_spec[str(vals[0])] = vals[1]
                    self.specification = dict_spec.get(str(fp[0]), "")
                    if not self.specification:
                        self.errors = "<%s> not include this port"%self.testfile
                        return
                    continue

                data = tstd_p.findall(line)
                if data:
                    if self.testdata == "0.0":
                        self.testdata = data[0]
                    else:
                        if float(self.testdata) < float(data[0]):
                            self.testdata = data[0]
                    continue
                user = user_p.findall(line)
                if user:
                    self.operator = str(user[0]).strip()
                version = vers_p.findall(line)
                if version:
                    self.version = str(version[0]).strip()
                machine = mach_p.findall(line)
                if machine:
                    self.machine = str(machine[0][0]).strip() + " " + str(machine[0][1]).strip()
                testtime = time_p.findall(line)
                if testtime:
                    self.testtime = str(testtime[0]).strip()
                currline = line_p.findall(line)
                if currline:
                    self.line = str(currline[0]).strip()
                    break
            if float(self.testdata) > float(self.specification):
                self.result = "FAIL"
        except Exception, e:
            errlist.append("analysis pas file error: %s"%e)
        finally:
            if f:
                f.close()

        if self.operator:
            sql = """
                SELECT COUNT(ID) FROM ShopFloorControl.dbo.SFC_EMPLOYEE(nolock) WHERE ID=%s
            """
            rst = getOneRow(self.db, sql, self.operator)
            if int(rst[0]) == 0:
                self.errors = "<%s> nauthorized"%self.operator

class DcfObj(BaseTestObj):
    def __init__(self, path, dbname):
        super(DcfObj, self).__init__(path, dbname)
        self.station    = ""
        self.tester     = ""
        self.tooling    = ""
        self.escptime   = ""
        self.location   = ""
        self.prodtype   = ""
        self.details    = []

    def save(self):
        if not self.db:
            errlist.append("Can't connected to DATABASE: %s"%self.dbname)
            raise Exception, "Error occured: Can't connected to DATABASE."

        sql = "sp_SSNTestStationFlow %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s"
        rst = getOneRow(self.db, sql, self.ssns, self.station, self.line, self.tester, self.machine, self.tooling, 
            self.testtime, self.escptime, self.location, self.prodtype, self.result, self.version, self.savepath, 
            "", self.progtype, self.operator)
        self.db.commit()

        if rst[0] == 0:
            sql = "sp_SSNTestDetailInfo %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s"
            for detail in self.details:
                testname    = str(detail[0]).strip()
                result      = str(detail[1]).strip()
                testdata    = str(detail[2]).strip()
                testunit    = str(detail[3]).strip()
                # detail[4] start frequency
                # detail[5] stop frequency
                lowlimit    = str(detail[6]).strip()
                uprlimit    = str(detail[7]).strip()
                testtype    = str(detail[8]).strip()
                rst = getOneRow(self.db, sql, self.ssns, self.station, testname, testtype, testdata, testunit, 
                    lowlimit, uprlimit, self.tester, self.machine, self.tooling, result, self.operator)
                self.db.commit()

    def analysis(self):
        f       = None
        main_p  = re.compile(MAIN_PATTERN)
        info_p  = re.compile(INFO_PATTERN)
        stop_p  = re.compile(STOP_PATTERN)
        try:
            f       = open(self.testfile, 'r')
            lines   = f.readlines()
            for line in lines:
                m = main_p.findall(line)
                if m:
                    infos           = list(m[0])
                    self.testtime   = str(infos[0]).strip()
                    self.station    = str(infos[1]).strip()
                    self.location   = str(infos[2]).strip()
                    self.line       = str(infos[3]).strip()
                    self.tester     = str(infos[4]).strip()
                    self.machine    = str(infos[5]).strip()
                    self.progtype   = str(infos[6]).strip()
                    self.tooling    = str(infos[7]).strip()
                    self.prodtype   = str(infos[8]).strip()
                    self.ssns       = str(infos[9]).strip()
                    self.operator   = str(infos[10]).strip()
                    continue
                d = info_p.findall(line)
                if d:
                    self.details.append(d[0])
                    continue
                e = stop_p.findall(line)
                if e:
                    infos           = list(e[0])
                    self.result     = str(infos[0]).strip()
                    self.escptime   = str(infos[1]).strip()
                    self.version    = str(infos[2]).strip()
                    break
        except Exception, e:
            errlist.append("analysis dcf file error: %s"%e)
        finally:
            if f:
                f.close()

class PhaObj(BaseTestObj):
    def __init__(self, path, dbname):
        super(PhaObj, self).__init__(path, dbname)
        self.station    = ""
        self.tester     = ""
        self.tooling    = ""
        self.escptime   = ""
        self.location   = ""
        self.prodtype   = ""
        self.frequency  = ""
        self.details    = []

    def save(self):
        if not self.db:
            errlist.append("Can't connected to DATABASE: %s"%self.dbname)
            raise Exception, "Error occured: Can't connected to DATABASE."

        beamp_rp = re.compile(beamprp)
        beamw_rp = re.compile(beamwrp)

        sql = "sp_SSNTestStationFlow %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s"
        rst = getOneRow(self.db, sql, self.ssns, self.station, self.line, self.tester, self.machine, self.tooling, 
            self.testtime, self.escptime, self.location, self.prodtype, self.result, self.version, self.savepath, 
            "", self.progtype, self.operator)
        self.db.commit()

        if rst[0] == 0:
            sql = "sp_SSNTestDetailForAntenna %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s"
            for detail in self.details:
                testname    = str(detail[0]).strip()
                result      = str(detail[1]).strip()
                testdata    = str(detail[2]).strip()
                testunit    = str(detail[3]).strip()
                startfreq   = str(detail[4]).strip()
                stopfreq    = str(detail[5]).strip()
                lowlimit    = str(detail[6]).strip()
                uprlimit    = str(detail[7]).strip()
                testtype    = str(detail[8]).strip()
                t = beamp_rp.findall(testname)
                if t:
                    testname  = "Beam Peak"
                    self.port = "CH%s"%str(t[0])
                else:
                    t = beamw_rp.findall(testname)
                    if t:
                        testname  = "Beam Width"
                        self.port = "CH%s"%str(t[0])
                    else:
                        self.port = ""
                if testname in ("Beam Peak", "Beam Width"):
                    lowlimit = float(lowlimit)
                    uprlimit = float(uprlimit)
                    valStart = (lowlimit + uprlimit) / 2.0
                    valStop  = (uprlimit - lowlimit) / 2.0
                    if valStart % 1 == 0:
                        valStart = int(valStart)
                    if valStop % 1 == 0:
                        valStop = int(valStop)
                    self.specification = "1"
                    lowlimit = str(valStart)
                    uprlimit = str(valStop) 
                else:
                    if lowlimit and uprlimit:
                        self.specification = lowlimit + " ~ " + uprlimit
                    elif lowlimit and not uprlimit:
                        self.specification = lowlimit
                    elif not lowlimit and uprlimit:
                        self.specification = uprlimit
                    else:
                        self.specification = ""
                if startfreq and stopfreq:
                    self.frequency = startfreq + "-" + stopfreq
                elif startfreq and not stopfreq:
                    self.frequency = startfreq
                elif not startfreq and stopfreq:
                    self.frequency = stopfreq
                else:
                    self.frequency = ""
                rst = getOneRow(self.db, sql, self.ssns, self.station, testname, testtype, testdata, testunit, 
                    self.frequency, self.port, self.specification, lowlimit, uprlimit, self.savepath, result, self.operator)
                self.db.commit()

    def analysis(self):
        f       = None
        main_p  = re.compile(MAIN_PATTERN)
        info_p  = re.compile(INFO_PATTERN)
        stop_p  = re.compile(STOP_PATTERN)
        try:
            f       = open(self.testfile, 'r')
            lines   = f.readlines()
            for line in lines:
                m = main_p.findall(line)
                if m:
                    infos           = list(m[0])
                    self.testtime   = str(infos[0]).strip()
                    self.station    = str(infos[1]).strip()
                    self.location   = str(infos[2]).strip()
                    self.line       = str(infos[3]).strip()
                    self.tester     = str(infos[4]).strip()
                    self.machine    = str(infos[5]).strip()
                    self.progtype   = str(infos[6]).strip()
                    self.tooling    = str(infos[7]).strip()
                    self.prodtype   = str(infos[8]).strip()
                    self.ssns       = str(infos[9]).strip()
                    self.operator   = str(infos[10]).strip()
                    continue
                d = info_p.findall(line)
                if d:
                    self.details.append(d[0])
                    continue
                e = stop_p.findall(line)
                if e:
                    infos           = list(e[0])
                    self.result     = str(infos[0]).strip()
                    self.escptime   = str(infos[1]).strip()
                    self.version    = str(infos[2]).strip()
                    break
        except Exception, e:
            errlist.append("analysis dcf file error: %s"%e)
        finally:
            if f:
                f.close()

class SwrObj(BaseTestObj):
    def __init__(self, path, dbname):
        super(SwrObj, self).__init__(path, dbname)
        self.station    = ""
        self.tester     = ""
        self.tooling    = ""
        self.escptime   = ""
        self.location   = ""
        self.prodtype   = ""
        self.frequency  = ""
        self.details    = []

    def save(self):
        if not self.db:
            errlist.append("Can't connected to DATABASE: %s"%self.dbname)
            raise Exception, "Error occured: Can't connected to DATABASE."
        
        vswrs_rp = re.compile(vswrsrp)
        isola_rp = re.compile(isolarp)
        pre_str  = ""
        if self.station == "WO TESTING-VSWR&ISO":
            pre_str = "Pre "

        sql = "sp_SSNTestStationFlow %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s"
        rst = getOneRow(self.db, sql, self.ssns, self.station, self.line, self.tester, self.machine, self.tooling, 
            self.testtime, self.escptime, self.location, self.prodtype, self.result, self.version, self.savepath, 
            "", self.progtype, self.operator)
        self.db.commit()
        
        if rst[0] == 0:
            sql = "sp_SSNTestDetailForAntenna %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s"
            for detail in self.details:
                testname    = str(detail[0]).strip()
                result      = str(detail[1]).strip()
                testdata    = str(detail[2]).strip()
                testunit    = str(detail[3]).strip()
                startfreq   = str(detail[4]).strip()
                stopfreq    = str(detail[5]).strip()
                lowlimit    = str(detail[6]).strip()
                uprlimit    = str(detail[7]).strip()
                testtype    = str(detail[8]).strip()
                t = vswrs_rp.findall(testname)
                if t:
                    testname  = "VSWR"
                    self.port = "CH%s"%str(t[0])
                else:
                    t = isola_rp.findall(testname)
                    if t:
                        testname  = "Isolation(dB)"
                        self.port = "CH%s->CH%s"%(str(t[0][0]), str(t[0][1]))
                    else:
                        self.port = ""
                if testname in ("VSWR", "Isolation(dB)"):
                    if uprlimit:
                        self.specification = "2"

                    if startfreq and stopfreq:
                        self.frequency = startfreq + "-" + stopfreq + " MHz"
                    elif startfreq and not stopfreq:
                        self.frequency = startfreq + " MHz"
                    elif not startfreq and stopfreq:
                        self.frequency = stopfreq + " MHz"
                    else:
                        self.frequency = ""
                else:
                    if lowlimit and uprlimit:
                        self.specification = lowlimit + " ~ " + uprlimit
                    elif lowlimit and not uprlimit:
                        self.specification = lowlimit
                    elif not lowlimit and uprlimit:
                        self.specification = uprlimit
                    else:
                        self.specification = ""
                    
                    if startfreq and stopfreq:
                        self.frequency = startfreq + " ~ " + stopfreq
                    elif startfreq and not stopfreq:
                        self.frequency = startfreq
                    elif not startfreq and stopfreq:
                        self.frequency = stopfreq
                    else:
                        self.frequency = ""
                testname = pre_str + testname
                rst = getOneRow(self.db, sql, self.ssns, self.station, testname, testtype, testdata, testunit, 
                    self.frequency, self.port, self.specification, lowlimit, uprlimit, self.savepath, result, self.operator)
                self.db.commit()

    def analysis(self):
        f       = None
        main_p  = re.compile(MAIN_PATTERN)
        info_p  = re.compile(INFO_PATTERN)
        stop_p  = re.compile(STOP_PATTERN)
        try:
            f       = open(self.testfile, 'r')
            lines   = f.readlines()
            for line in lines:
                m = main_p.findall(line)
                if m:
                    infos           = list(m[0])
                    self.testtime   = str(infos[0]).strip()
                    self.station    = str(infos[1]).strip()
                    self.location   = str(infos[2]).strip()
                    self.line       = str(infos[3]).strip()
                    self.tester     = str(infos[4]).strip()
                    self.machine    = str(infos[5]).strip()
                    self.progtype   = str(infos[6]).strip()
                    self.tooling    = str(infos[7]).strip()
                    self.prodtype   = str(infos[8]).strip()
                    self.ssns       = str(infos[9]).strip()
                    self.operator   = str(infos[10]).strip()
                    continue
                d = info_p.findall(line)
                if d:
                    self.details.append(d[0])
                    continue
                e = stop_p.findall(line)
                if e:
                    infos           = list(e[0])
                    self.result     = str(infos[0]).strip()
                    self.escptime   = str(infos[1]).strip()
                    self.version    = str(infos[2]).strip()
                    break
        except Exception, e:
            errlist.append("analysis dcf file error: %s"%e)
        finally:
            if f:
                f.close()

def getFileByExt():
    filemap = {
        ".flg": [".dcf", DcfObj, "SFPRD02"], 
        ".ant": [".txt", PiaObj, "SFPRD01"], 
        ".pas": [".txt", PasObj, "SFPRD02"],
        ".pha": [".dcf", PhaObj, "SFPRD01"],
        ".swr": [".dcf", SwrObj, "SFPRD01"]
    }
    files = os.listdir(RESULTPATH)
    for f in files:
        _file = os.path.join(RESULTPATH, f)

        if not os.path.isfile(_file):
            continue

        name, ext = os.path.splitext(_file)
        if ext not in filemap.keys():
            continue

        try:
            if os.path.isfile(_file):
                os.remove(_file)
        except Exception, e:
            errlist.append("remove file error: %s"%e)

        ext     = ext.lower()
        newObj  = None
        mapObj  = filemap.get(ext, [])
        if not mapObj:
            continue

        fileext, obj, dbname = mapObj
        info_file = name + fileext
        info_file = os.path.join(RESULTPATH, info_file)
        if not os.path.exists(info_file):
            errlist.append("file not found: %s"%info_file)
            continue

        newObj = obj(info_file, dbname)

        newObj.analysis()
        if newObj.errors:
            errlist.append(newObj.errors)
            try:
                os.remove(newObj.testfile)
            except Exception, e:
                errlist.append("remove file error: %s"%e)
            continue

        year        = str(datetime.date.today().year)
        month       = str(datetime.date.today().month)
        day         = str(datetime.date.today().day)
        rand        = str(time.time())
        filename    = os.path.basename(newObj.testfile)
        target, exp = os.path.splitext(filename)
        target      = target.strip() + '_' + rand + fileext

        newObj.savepath = os.path.join(BACKUPPATH, year, month, day, target)
        newObj.filedict[newObj.savepath] = newObj.testfile
        newObj.save()
        newObj.backupFile()

        try:
            os.remove(newObj.testfile)
        except Exception, e:
            errlist.append("remove file error: %s"%e)

def getOneRow(db, sql, *parameter):
    try:
        cur = db.cursor()
        if parameter:
            cur.execute(sql, parameter)
        else:
            cur.execute(sql)
        return cur.fetchone()
    except Exception, e:
        errlist.append(e)

    return []

def getAllRow(db, sql, *parameter):
    try:
        cur = db.cursor()
        if parameter:
            cur.execute(sql, parameter)
        else:
            cur.execute(sql)
        return cur.fetchall()
    except Exception, e:
        errlist.append(e)

    return []

def execNonData(db, sql, *parameter):
    try:
        cur = db.cursor()
        if parameter:
            cur.execute(sql, parameter)
        else:
            cur.execute(sql)
    except Exception, e:
        errlist.append(e)

def send_mail(message):
    import smtplib
    admins = ['zhujianhua@rosenbergerap.com']
    from email.parser import Parser
    smtp = smtplib.SMTP('mailserver.rosenbergerap.com')

    msg = Parser().parsestr('From: <shopfloors@sys_auto.com>\n'
        'To: <zhujianhua@rosenbergerap.com>\n'
        'Subject: ShopFloorControl AutoTest occured some error\n'
        '\n'
        '%s'%message)
    smtp.sendmail(msg['From'], admins, msg.as_string())
    smtp.quit()

class SFCTestService(win32serviceutil.ServiceFramework): 
    """
    Usage: 'SFCTestService.py [options] install|update|remove|start [...]|stop|restart [...]|debug [...]'
    Options for 'install' and 'update' commands only:
     --username domain\username : The Username the service is to run under
     --password password : The password for the username
     --startup [manual|auto|disabled|delayed] : How the service starts, default = manual
     --interactive : Allow the service to interact with the desktop.
     --perfmonini file: .ini file to use for registering performance monitor data
     --perfmondll file: .dll file to use when querying the service for
       performance data, default = perfmondata.dll
    Options for 'start' and 'stop' commands only:
     --wait seconds: Wait for the service to actually start or stop.
                     If you specify --wait with the 'stop' option, the service
                     and all dependent services will be stopped, each waiting
                     the specified period.
    """
    _svc_name_          = "SFCTestService"
    _svc_display_name_  = "SFCTestService"
    _svc_description_   = "shop floor auto transfer test result service."

    def __init__(self, args): 
        win32serviceutil.ServiceFramework.__init__(self, args) 
        self.hWaitStop  = win32event.CreateEvent(None, 0, 0, None)
        self.logger     = self._getLogger()
        self.isAlive    = True

    def _getLogger(self):
        import logging
        import os
        import inspect

        logger      = logging.getLogger('[SFCTestService]')
        this_file   = inspect.getfile(inspect.currentframe())
        dirpath     = os.path.abspath(os.path.dirname(this_file))
        handler     = logging.FileHandler(os.path.join(dirpath, "service.log"))
        formatter   = logging.Formatter('%(asctime)s %(name)s %(levelname)-8s %(message)s')
        handler.setFormatter(formatter)

        logger.addHandler(handler)
        logger.setLevel(logging.INFO)

        return logger

    def SvcDoRun(self):
        import time
        self.logger.info("SFCTestService do run....")
        global errlist
        while self.isAlive:
            errlist = []
            getFileByExt()
            for err in errlist:
                self.logger.error(err)
            time.sleep(1)

    def SvcStop(self): 
        self.logger.info("SFCTestService do stop....")
        self.ReportServiceStatus(win32service.SERVICE_STOP_PENDING) 
        win32event.SetEvent(self.hWaitStop) 
        self.isAlive = False

if __name__ == "__main__":
    win32serviceutil.HandleCommandLine(SFCTestService)
